<?php
// +----------------------------------------------------------------------
// | 大客户分析
// +----------------------------------------------------------------------
namespace app\admin\controller\v2;
use app\admin\Adminbase;
use base\files\Poster;
use think\Db;
use app\admin\model\SystemParamValue;

class UserAnalysis extends Adminbase
{

    /**UserAnalysis
     * 获取年龄段消费数据
     * /admin/v2/user_analysis/index
     * 需要登录
     * @is_need_login false
     */
    public function index($shop_id,$date=''){
        try {
            $x = model("admin/system_param_value")
                    ->where('param_code','buess_shop_khfx_age')
                    ->where('parent_value',$shop_id)
                    ->value('param_value') ?? '0~100,100~500,500';

            $data['x'] =  explode(",",$x);
            $data['y1'] = [ 'number'=>[],'money'=>[] ];
            $data['y2'] = [ 'number'=>[],'money'=>[] ];


            foreach($data['x'] as $key => $value){
                $arr = explode("~",$value);
                $model = $this->getModel($shop_id,1,$date);

                if($arr[1]){
                    $data['y1']['number'][] = $num = $model
                        ->where('a.age','>=',$arr[0])
                        ->where('a.age','<=',$arr[1])
                        ->count();

                    $data['y1']['money'][] = $money = $model
                        ->where('a.age','>=',$arr[0])
                        ->where('a.age','<=',$arr[1])
                        ->sum('pay_price');


                }else{

                    $data['y1']['number'][] =  $num = $model->where('a.age','>',$arr[0])->count();
                    $data['y1']['money'][] = $model
                        ->where('a.age','>',$arr[0])
                        ->sum('pay_price');

                }

                $model = $this->getModel($shop_id,2,$date);

                if($arr[1]){
                    $data['y2']['number'][] = $num = $model
                        ->where('a.age','>=',$arr[0])
                        ->where('a.age','<=',$arr[1])
                        ->count();

                    $data['y2']['money'][] = $money = $model
                        ->where('a.age','>=',$arr[0])
                        ->where('a.age','<=',$arr[1])
                        ->sum('pay_price');

                }else{
                    $data['y2']['number'][] = $num = $model->where('b.pay_price','>',$arr[0])->count();
                    $data['y2']['money'][] = $totalNum = $model
                        ->where('a.age','>',$arr[0])
                        ->sum('pay_price');

                }

            }

            return $this->ReSucess($data);

        } catch (\Exception $e) {
            return $this->ReError($e->getMessage());
        }
    }

    /**
     *
     * 设置客户年龄段
     * /admin/v2/user_analysis/setAge
     * 需要登录
     * @is_need_login false
     */
    public function setAge($shop_id,$data){
        try {
            $res = model("admin/system_param_value")
                ->where('param_code','buess_shop_khfx_age')
                ->where('parent_value',$shop_id)
                ->delete();
            $res = model("admin/system_param_value")->save([
                'param_scope'=>'店铺参数',
                'param_code'=>'buess_shop_khfx_age',
                'parent_value'=>$shop_id,
                'param_value'=>$data,
            ]);
            return $this->ReSucess(true);
        } catch (\Exception $e) {
            return $this->ReError($e->getMessage());
        }
    }

    /**
     * 获取大客户数据
     * /admin/v2/user_analysis/getAge
     * 需要登录
     * @is_need_login false
     */
    public function getAge($shop_id){
        try {
            $result = model("admin/system_param_value")
                    ->where('param_code','buess_shop_khfx_age')
                    ->where('parent_value',$shop_id)
                    ->value('param_value') ?? '';
            return $this->ReSucess($result);
        } catch (\Exception $e) {
            return $this->ReError($e->getMessage());
        }
    }

    //获取模型
    private function getModel($shop_id=-1,$sex=1,$dates=''){

        $sql = '1=1';
        if($dates){
            $sql =  'add_time>='.explode(" - ",$dates)[0];
            $sql =  $sql.' and add_time<='.explode(" - ",$dates)[1];
        }

        $model = model('user')->alias('a')
            ->leftJoin("( 
				select uid,sum(pay_price) pay_price from xw_order 
				where is_pay = 1 and status >= 0 
				and $sql
				group by uid
			) b",'a.id = b.uid')

            ->where('a.shop_id',$shop_id)
            ->where(function($query) use($sex) {
                $query->where("a.sex",$sex);
                $query->whereor("a.sex", $sex == 1 ? '男' : '女' );
            });

        return $model;
    }



    /**UserAnalysis
     * 获取客户对比
     * /admin/v2/user_analysis/getNewOldUser
     * 需要登录
     * @is_need_login false
     */
    public function getNewOldUser($shop_id,$year,$month){
        try {

            //获取等级会员名称
            $data['xArr'] = Db::name('user_level')->field('level,name')->select();
            $result_data=[];

            $model = $this->getNewOldCustomers($shop_id,$year,$month);
            $result_new=$model->field('b.date,sum(b.pay_num) as new_num')->group('b.date')
                ->where('b.pay_num','>',1)
                ->select();

            $model_old = $this->getNewOldCustomers($shop_id,$year,$month);
            $result_old=$model_old->field('b.date,sum(b.pay_num) as old_num')->group('b.date')
                ->where('b.pay_num',1)
                ->select();


            //新老客户融合
            $yearMonth='';
            if($year){
                if(!$month){
                    $yearMonth=$year.'-'.date("m-d");
                }else{
                    $yearMonth=$year.'-'.$month.'-01';
                }
            }

            $month_days=date('t',strtotime($yearMonth));

            $result_census=[];
            for ($i=1;$i<=$month_days;$i++){

                if(count($result_new)){

                    for($j=0;$j<count($result_new);$j++){
                        if($i==$result_new[$j]['date']){
                            $result_census[$i]['new_num']=$result_new[$j]['new_num'];
                            break;
                        }else{
                            $result_census[$i]['new_num']=0;
                        }
                    }

                }else{

                    $result_census[$i]['new_num']=0;
                }
                //旧用户
                if(count($result_old)){

                    for($j=0;$j<count($result_old);$j++){
                        if($i==$result_old[$j]['date']){
                            $result_census[$i]['old_num']=$result_old[$j]['old_num'];
                            break;
                        }else{
                            $result_census[$i]['old_num']=0;

                        }
                    }

                }else{

                    $result_census[$i]['old_num']=0;
                }

            }
            $result[]=$result_census;
            return $this->ReSucess($result);

        } catch (\Exception $e) {
            return $this->ReError($e->getMessage());
        }
    }

    //获取新老客户模型
    public function getNewOldModel($shop_id,$year,$month){
        $sql = '1=1';

        //1年不为空，月为空，默认是年+当前时间的月份。
        if($year){
            if(!$month){
                $yearMonth=$year.'-'.date("m-d");
                $sql =  $sql." and DATE_FORMAT(add_time,'%Y%m')=DATE_FORMAT('$yearMonth','%Y%m')";
            }else{
                $yearMonth=$year.'-'.$month.'-01';
                $sql =  $sql." and DATE_FORMAT(add_time,'%Y%m')=DATE_FORMAT('$yearMonth','%Y%m')";
            }
        }

        $model = model('user')
            ->field('a.id,a.nickname,c.level,c.`name` as level_name,count(d.uid) as new_num')->alias('a')
            ->leftJoin("(
               xw_user_vip b
            )","b.uid=a.id")
            ->leftJoin("(
               xw_user_level c 
            )","c.level=b.level")
            ->leftJoin("( 
				select uid,count(id) as pay_num from xw_order 
				where is_pay = 1 and status >= 0 
				and $sql
				group by uid
			) d",'d.uid=a.id')

            ->where('a.shop_id',$shop_id);

        return $model;
    }

    /**UserAnalysis
     * 获取新客户数据,消费结构
     * /admin/v2/user_analysis/getNewConsume
     * 需要登录
     * @is_need_login false
     */
    public function getNewConsume($shop_id,$year='',$month=''){
        try {

            //获取等级会员名称

            $data['xArr'] = Db::name('user_level')->field('level,name')->select();

            $result_data=[];


            //得到等级下的每一个统计
            $model = $this->getUserModel($shop_id,$year,$month);
            $result_new=$model->where('d.pay_num',1)->select();



            foreach($data['xArr'] as $key => $value){

                if(count($result_new)){

                    foreach ($result_new as $k => $v) {

                        if($value['level']==$v['level']){
                            $result_data[$key]['name'] = $value['name'];
                            $result_data[$key]['number'] = $v['new_num']?$v['new_num']:0;
                            break;
                        }else{
                            $result_data[$key]['name'] = $value['name'];
                            $result_data[$key]['number'] = 0;
                        }


                    }
                }else{

                    $result_data[$key]['name']=$value['name'];
                    $result_data[$key]['number']=0;
                }




            }

            return $this->ReSucess($result_data);

        } catch (\Exception $e) {
            return $this->ReError($e->getMessage());
        }
    }

    /**UserAnalysis
     * 获取客户结构
     * /admin/v2/user_analysis/getUserJg
     * 需要登录
     * @is_need_login false
     */
    public function getUserJg($shop_id,$year='',$month=''){
        try {


            $result_data=[];


            //得到vip男的
            $model_vip_man = $this->getUserVipModel($shop_id,1,$year,$month)->count('c.uid');

            //得到vip男的
            $model_vip_women = $this->getUserVipModel($shop_id,2,$year,$month)->count('c.uid');

            //得到散客男的
            $not_vip_man = $this->getNotUserVipModel($shop_id,1,$year,$month)->count('c.uid');

            //得到散客女的
            $not_vip_women = $this->getNotUserVipModel($shop_id,2,$year,$month)->count('c.uid');


            $result_data[0]['name'] = '会员男';
            $result_data[0]['number'] = $model_vip_man;

            $result_data[1]['name'] = '会员女';
            $result_data[1]['number'] = $model_vip_women;

            $result_data[2]['name'] ='散客男';
            $result_data[2]['number'] = $not_vip_man;

            $result_data[3]['name'] = '散客女';
            $result_data[3]['number'] = $not_vip_women;



            return $this->ReSucess($result_data);

        } catch (\Exception $e) {
            return $this->ReError($e->getMessage());
        }
    }

    //获取各分类的新顾客模型
    private function getUserModel($shop_id=-1,$year='',$month=''){

        $sql = '1=1';

        //1年不为空，月为空，默认是年+当前时间的月份。
        if($year){
            if(!$month){
                $yearMonth=$year.'-'.date("m-d");
                $sql =  $sql." and DATE_FORMAT(add_time,'%Y%m')=DATE_FORMAT('$yearMonth','%Y%m')";
            }else{
                $yearMonth=$year.'-'.$month.'-01';
                $sql =  $sql." and DATE_FORMAT(add_time,'%Y%m')=DATE_FORMAT('$yearMonth','%Y%m')";
            }
        }

        $model = model('user')
            ->field('a.id,a.nickname,c.level,c.`name` as level_name,count(d.uid) as new_num')->alias('a')
            ->leftJoin("(
               xw_user_vip b
            )","b.uid=a.id")
            ->leftJoin("(
               xw_user_level c 
            )","c.level=b.level")
            ->leftJoin("( 
				select uid,count(id) as pay_num from xw_order 
				where is_pay = 1 and status >= 0 
				and $sql
				group by uid
			) d",'d.uid=a.id')

            ->where('a.shop_id',$shop_id);

        return $model;
    }




    //客户结构分析
    private function getJgModel($shop_id=-1,$sex=1,$year,$month){
        $sql = '1=1';

        //1年不为空，月为空，默认是年+当前时间的月份。
        if($year){
            if(!$month){
                $yearMonth=$year.'-'.date("m-d");
                $sql =  $sql." and DATE_FORMAT(add_time,'%Y%m')=DATE_FORMAT('$yearMonth','%Y%m')";
            }else {
                $yearMonth = $year . '-' . $month . '-01';
                $sql = $sql . " and DATE_FORMAT(add_time,'%Y%m')=DATE_FORMAT('$yearMonth','%Y%m')";
            }
        }

        $model = model('user')->alias('a')
            ->leftJoin("( 
				select uid,sum(pay_price) pay_price from xw_order 
				where is_pay = 1 and status >= 0 
				and $sql
				group by uid
			) b",'a.id = b.uid')

            ->where('a.shop_id',$shop_id)
            ->where(function($query) use($sex) {
                $query->where("a.sex",$sex);
                $query->whereor("a.sex", $sex == 1 ? '男' : '女' );
            });




        return $model;
    }


    //客户结构分（会员，男和女的统计）
    public function getUserVipModel($shop_id=-1,$sex=1,$year,$month){
        $sql = '1=1';

        //1年不为空，月为空，默认是年+当前时间的月份。
        if($year){
            if(!$month){
                $yearMonth=$year.'-'.date("m-d");
                $sql =  $sql." and DATE_FORMAT(add_time,'%Y%m')=DATE_FORMAT('$yearMonth','%Y%m')";
            }else{
                $yearMonth=$year.'-'.$month.'-01';
                $sql =  $sql." and DATE_FORMAT(add_time,'%Y%m')=DATE_FORMAT('$yearMonth','%Y%m')";
            }
        }

        $model = model('user')->alias('a')
            ->leftJoin("xw_user_vip b","a.id=b.uid")
            ->leftJoin("( 
				select uid,count(pay_price) tp_count from xw_order 
				where is_pay = 1 and status >= 0 
				and $sql
				group by uid
			) c",'a.id = b.uid')

            ->where('a.shop_id',$shop_id)
            ->where(function($query) use($sex) {
                $query->where("a.sex",$sex);
                $query->whereor("a.sex", $sex == 1 ? '男' : '女' );

            });


        return $model;
    }

    //客户结构分（散客，男和女的统计）
    public function getNotUserVipModel($shop_id=-1,$sex=1,$year,$month){
        $sql = '1=1';

        //1年不为空，月为空，默认是年+当前时间的月份。
        if($year){
            if(!$month){
                $yearMonth=$year.'-'.date("m-d");
                $sql =  $sql." and DATE_FORMAT(add_time,'%Y%m')=DATE_FORMAT('$yearMonth','%Y%m')";
            }else{
                $yearMonth=$year.'-'.$month.'-01';
                $sql =  $sql." and DATE_FORMAT(add_time,'%Y%m')=DATE_FORMAT('$yearMonth','%Y%m')";
            }
        }

        $model = model('user')->alias('a')
            ->leftJoin("xw_user_vip b","a.id<>b.uid")
            ->leftJoin("( 
				select uid,count(pay_price) tp_count from xw_order 
				where is_pay = 1 and status >= 0 
				and $sql
				group by uid
			) c",'a.id = b.uid')

            ->where('a.shop_id',$shop_id)
            ->where(function($query) use($sex) {
                $query->where("a.sex",$sex);
                $query->whereor("a.sex", $sex == 1 ? '男' : '女' );

            });


        return $model;
    }


    //获取新老客户的模型
    public function getNewOldCustomers($shop_id,$year,$month){
        $sql = '1=1';

        //1年不为空，月为空，默认是年+当前时间的月份。
        if($year){
            if(!$month){
                $yearMonth=$year.'-'.date("m-d");
                $sql =  $sql." and DATE_FORMAT(add_time,'%Y%m')=DATE_FORMAT('$yearMonth','%Y%m')";
            }else{
                $yearMonth=$year.'-'.$month.'-01';
                $sql =  $sql." and DATE_FORMAT(add_time,'%Y%m')=DATE_FORMAT('$yearMonth','%Y%m')";
            }
        }

        $model = model('user')->alias('a')
            ->leftJoin("( 
				select uid,date_format(add_time,'%e') as date,count(id) as pay_num from xw_order 
				where is_pay = 1 and status >= 0 
				and $sql
				group by uid
			) b",'a.id = b.uid')

            ->where('a.shop_id',$shop_id);


        return $model;
    }



    //获取每个月具体的天数
    function get_day($date)
    {
        $tem = explode('-',$date);
        $year = $tem['0'];
        $month = $tem['1'];
        if(in_array($month ,array('1','3','5','7','8','01','03','05','07','08','10','12')))
        {

            $text = '31';
        }
        elseif( $month == 2 )
        {
            if ( $year%400 == 0 || ($year%4 == 0 && $year%100 !== 0) )    //判断是否是闰年
            {

                $text = '29';
            }
            else{

                $text = '28';
            }
        }
        else{

            $text = '30';
        }
        return $text;
    }


}

